﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace FbKPI.Controller.dbConnection
{
    public class ConnectionClass
    {
        private string connstring = ConfigurationManager.ConnectionStrings["connstring"].ToString();
        private SqlConnection conn;

        public ConnectionClass()
        {
            conn = new SqlConnection(connstring);
        }

        public DataSet GetTable(SqlCommand command)
        {
            DataSet ds = new DataSet();
            command.Connection = conn;
            if (conn.State != ConnectionState.Open)
                conn.Close();
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(ds);
            conn.Close();
            return ds;
        }

        public IList<string> GetSingleRow(SqlCommand command)
        {
            if (conn.State != ConnectionState.Open)
                conn.Close();
            command.Connection = conn;
            conn.Open();
            SqlDataReader dr = command.ExecuteReader();
            IList<string> retVal = null;
            if (dr.HasRows)
            {
                retVal = new List<string>();
                while (dr.Read())
                {
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        retVal.Add(dr.GetValue(i).ToString());
                    }
                }
            }
            dr.Close();
            conn.Close();
            return retVal;
        }

        public string GetSingleData(SqlCommand command)
        {
            if (conn.State != ConnectionState.Open)
                conn.Close();
            command.Connection = conn;
            conn.Open();
            var retVal = command.ExecuteScalar();
            conn.Close();
            return retVal.ToString();
        }

        public string ExecuteNonQuery(SqlCommand command)
        {
            if (conn.State != ConnectionState.Open)
                conn.Close();
            command.Connection = conn;
            conn.Open();
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //log
                conn.Close();
                return "Transaction Failed : " + ex.Message.ToString();
            }
            conn.Close();
            return "Transaction Success";
        }
    }
}